using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Windows.Forms;

namespace Cache
{
    
    class Options
    {
        public void SaveOptions(string OptID, string OptValue)
        {

            SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\GeoCaches.db");

            SQLiteDataReader rdr = null;
            SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM tblOptions WHERE OptionID = '" + OptID + "'", conn);

            conn.Open();
            try
            {
                rdr = cmd.ExecuteReader();

                //Check to see if rows exist.  If they do then an update needs to be done not an add.
                if (rdr.HasRows == true)
                {
                    //If the name exists then run an update in the database.
                    //MessageBox.Show("Update Record");

                    //Need to close the reader and the connection to the DB otherwise the file is locked.
                    rdr.Close();
                    conn.Close();
                    
                    UpdateOption(OptID, OptValue);
                }
                else
                {
                    //If no rows exist then we will add the record for the first time.
                    //TODO: Add the "ADD()" function here.
                    rdr.Close();
                    conn.Close();

                    AddOption(OptID, OptValue);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            rdr.Close();
            conn.Close();
        }

        private void UpdateOption(string OptID, string OptValue)
        {
            SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\GeoCaches.db");

            conn.Open();
            string mySQL;

            mySQL = "UPDATE tblOptions SET OptionID = '" + OptID + "', OptionValue = '" + OptValue + "' WHERE OptionID = '" + OptID + "'";

            SQLiteCommand cmd = new SQLiteCommand(mySQL, conn);

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString() + "\r\n\r\nSQL Statement: " + mySQL);
            }

        }

        private void AddOption(string OptID, string OptValue)
        {
            SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\GeoCaches.db");
            SQLiteDataReader rdr = null;

            conn.Open();

            string mySQL;

            mySQL = "INSERT INTO tblOptions (OptionID, OptionValue) VALUES ('" + OptID + "', '" + OptValue + "')";

            SQLiteCommand cmd = new SQLiteCommand(mySQL, conn);

            try
            {
                rdr = cmd.ExecuteReader();

                rdr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString() + "\r\n\r\nSQL: " + mySQL);
            }
        }

        public string GetOption(string OptID)
        {
            SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\GeoCaches.db");

            SQLiteDataReader rdr = null;
            SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM tblOptions WHERE OptionID = '" + OptID + "'", conn);

            string Option = null;
            conn.Open();

            try
            {
                rdr = cmd.ExecuteReader();

                if (rdr.HasRows == true)
                {
                    Option = rdr["OptionValue"].ToString();
                }
                else
                {
                    Option = "";
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            rdr.Close();
            conn.Close();
            return Option;
        }//End GetOptions

        public string GetHomeLocation(string LocationID, string ReturnItem)
        {
            string SQL = "SELECT * FROM tblHomeLocations WHERE HomeID = '" + LocationID + "'";
            string OptValue = null;

            SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\GeoCaches.db");

            SQLiteDataReader rdr = null;
            SQLiteCommand cmd = new SQLiteCommand(SQL, conn);

            conn.Open();
            try
            {
                rdr = cmd.ExecuteReader();
                if (rdr.HasRows == true)
                {
                    OptValue = rdr[ReturnItem].ToString();
                }
                else
                {
                    OptValue = "ERROR";
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex + "\r\nSQL: " + SQL);
            }
            //Close the reader and the connection.
            rdr.Close();
            conn.Close();

            return OptValue;
        }

        public string GetCacheTableView()
        {
            SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\GeoCaches.db");

            SQLiteDataReader rdr = null;
            SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM tblOptions WHERE OptionID LIKE 'CacheColumn-%'", conn);

            conn.Open();
            
            rdr = cmd.ExecuteReader();
            
            string CacheColumns = null;
            
            try
            {
                while (rdr.Read())
                {
                    if (rdr["OptionValue"].ToString() == "True")
                    {
                        switch (rdr["OptionID"].ToString())
                        {
                            case "CacheColumn-Short_Description":
                                CacheColumns += "ShortDescription, ";
                                break;

                            case "CacheColumn-Long_Description":
                                CacheColumns += "LongDescription, ";
                                break;

                            case "CacheColumn-Encoded_Hints":
                                CacheColumns += "EncodedHints, ";
                                break;

                            default:
                                CacheColumns += rdr["OptionID"].ToString().Replace("CacheColumn-", "") + ", ";
                                break;

                        }
                    }
                }
                CacheColumns = CacheColumns.Remove(CacheColumns.Length - 2);   
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            return CacheColumns;
        }

        public int GetVEMapZoomLevel()
        {
            int ZoomLevel = 3;

            SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\GeoCaches.db");

            SQLiteDataReader rdr = null;
            SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM tblOptions WHERE OptionID = 'MapZoomLevel'", conn);

            conn.Open();

            try
            {
                rdr = cmd.ExecuteReader();

                if (rdr.HasRows == true)
                {
                    switch (rdr["OptionValue"].ToString())
                    {
                        case "6000 Miles":
                            ZoomLevel = 1;
                            break;

                        case "3000 Miles":
                            ZoomLevel = 2;
                            break;

                        case "1500 Miles":
                            ZoomLevel = 3;
                            break;

                        case "700 Miles":
                            ZoomLevel = 4;
                            break;

                        case "300 Miles":
                            ZoomLevel = 5;
                            break;

                        case "150 Miles":
                            ZoomLevel = 6;
                            break;

                        case "90 Miles":
                            ZoomLevel = 7;
                            break;

                        case "40 Miles":
                            ZoomLevel = 8;
                            break;

                        case "20 Miles":
                            ZoomLevel = 9;
                            break;

                        case "10 Miles":
                            ZoomLevel = 10;
                            break;

                        case "6 Miles":
                            ZoomLevel = 11;
                            break;

                        case "3 Miles":
                            ZoomLevel = 12;
                            break;

                        case "1.5 Miles":
                            ZoomLevel = 13;
                            break;

                        case ".7 Miles":
                            ZoomLevel = 14;
                            break;

                        case "600 Yards":
                            ZoomLevel = 15;
                            break;

                        case "300 Yards":
                            ZoomLevel = 16;
                            break;

                        case "150 Yards":
                            ZoomLevel = 17;
                            break;

                        case "80 Yards":
                            ZoomLevel = 18;
                            break;

                        case "40 Yards":
                            ZoomLevel = 19;
                            break;
                    } // End Switch
                }
                else
                {
                    MessageBox.Show("Well the programmer messed that one Up!", "OOPS!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                } //END ELSE

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            rdr.Close();
            conn.Close();


            return ZoomLevel;
        }
    }
}
